package net.dodoman.miao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MsAccessDBUtils {
	private static MsAccessDBUtils instance = null;

	public static MsAccessDBUtils getInstance() {
		if (instance == null) {
			instance = new MsAccessDBUtils();
		}
		return instance;
	}

	private MsAccessDBUtils() {
	}

	// 最先被调用 mdbFile=d:\xxx.mdb
	public void loadConfig(String mdbFile, String user, String psw)
			throws Exception {
		mdb_file = mdbFile;
		this.user = user;
		pwd = psw;
		loadDriver();
	}

	public void loadConfig(String mdbFile) throws Exception {
		mdb_file = mdbFile;
		loadDriver();
	}

	private static String dirverClass = "sun.jdbc.odbc.JdbcOdbcDriver";
	// jdbc:odbc:Driver={MicroSoft Access Driver *.mdb)};DBQ = Northwind.mdb
	// jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\\test.mdb
	private static String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=";
	private static String mdb_file = null;// access 文件[这里要绝对路径]
	private static String user = "";
	private static String pwd = "";
	private static Connection conn;
	private static Statement stmt;
	private static ResultSet rs;

	private void loadDriver() throws Exception {
		try {
			Class.forName(dirverClass);
		} catch (Exception e) {
			throw e;
		}
	}

	// 创建不可滚动的连接
	public static void connect() throws Exception {
		try {
			System.out.println(url + mdb_file);
			System.out.println(user);
			System.out.println(pwd);
			conn = DriverManager.getConnection(url + mdb_file, user, pwd);
			stmt = conn.createStatement();
		} catch (Exception e) {
			throw e;
		}
	}

	public static void connect(boolean autocommit) throws Exception {
		try {
			conn = DriverManager.getConnection(url + mdb_file, user, pwd);
			conn.setAutoCommit(autocommit);
			stmt = conn.createStatement();
		} catch (Exception e) {
			throw e;
		}
	}

	// 创建可以滚动的连接
	public static void connect2() throws Exception {
		try {
			conn = DriverManager.getConnection(url + mdb_file, user, pwd);
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
		} catch (Exception e) {
			throw e;
		}
	}

	// 关闭连接
	public static void close() throws Exception {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (Exception e) {
			throw e;
		}
	}

	// 查询语句
	public static List executeQuery(String sql) throws Exception {
		List l = new ArrayList();
		try {
			if (stmt == null) {
				connect();
			}
			rs = stmt.executeQuery(sql);
			l = orgResultSet4List(rs);
			// close();
		} catch (Exception e) {
			throw e;
		}
		return l;
	}

	public static int executeUpdate(String sql) throws Exception {
		try {
			if (stmt == null) {
				connect();
			}
			int res = stmt.executeUpdate(sql);
			// close();
			return res;
		} catch (Exception e) {
			throw e;
		}
	}

	public static int[] executeUpdate(String[] sql) throws Exception {
		try {
			if (stmt == null) {
				connect(false);
			}
			for (int i = 0; i < sql.length; i++) {
				stmt.addBatch(sql[i]);
			}
			int[] res = stmt.executeBatch();
			conn.commit();
			// close();
			return res;
		} catch (Exception e) {
			throw e;
		}
	}

	private static List orgResultSet4List(ResultSet rs) throws Exception {
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			int cols = rsmd.getColumnCount();
			List l = new ArrayList();
			Map recordMap = null;
			while (rs.next()) {
				recordMap = new HashMap();
				for (int i = 0; i < cols; i++) {
					recordMap.put((String) (rsmd.getColumnName(i + 1))
							.toLowerCase(), rs.getObject(i + 1));
				}
				l.add(recordMap);
			}
			return l;
		} catch (Exception ex) {
			// ex.printStackTrace();
			throw ex;
		}
	}

	public static void main(String[] args) {
		if (args != null && args.length > 0) {
			System.out.println("args[0]=" + args[0]);
			try {
				MsAccessDBUtils.getInstance().loadConfig(args[0], "", "");
			} catch (Exception ex) {
				ex.printStackTrace();
				System.exit(-1);
			}
			MsAccessDBUtils.getInstance().go();
		} else {
			System.exit(0);
		}
	}

	void go() {
		try {
			for (int k = 0; k < 3; k++) {
				String sql = "insert into leave_words(cmp_name,tell,web_url,addr,mail,conts,notes)values";
				sql += "('公司名称2','0100003333','http://sdfa.com','北京大sdf砍刀饭卡第三','aa@aa.com','做得很好','[无]')";
				int res = MsAccessDBUtils.getInstance().executeUpdate(sql);
				System.out.println("insert=" + res);
				sql = "select * from leave_words";
				List l = MsAccessDBUtils.getInstance().executeQuery(sql);
				MsAccessDBUtils.getInstance().close();
				if (l != null) {
					System.out.println("==" + (l.get(0)).toString());
					java.text.SimpleDateFormat df = new java.text.SimpleDateFormat(
							"yyyy-MM-dd HH:mm:ss W E");
					Map rowMap;
					for (int i = 0; i < l.size(); i++) {
						rowMap = (Map) l.get(i);
						java.util.Date crt_time = new java.util.Date(
								((java.sql.Timestamp) rowMap.get("crt_time"))
										.getTime());
						System.out.println(df.format(crt_time));
					}

				}

			}

		} catch (Exception ex) {
			ex.printStackTrace();
			System.exit(-1);
		}
	}
}
